package com.ty.dao.impl;

import com.ty.bean.Express;
import com.ty.dao.BaseExpressDao;
import com.ty.exception.DuplicateCodeException;
import com.ty.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * SQL语句在执行的时候也会转成大写去执行，
 * 因此直接写成大写会提升一丢丢的性能。。。
 * ctrl + shift + u 大小写转换快捷键
 *
 * @author 田野
 */
public class BaseExpressDaoImpl implements BaseExpressDao {

    // 用于查询数据库中的全部快递(总数+新增) 待取件快递(总数+新增)
    private static final String DQL_CONSOLE = "SELECT " +
            " COUNT(ID) DATA1_SIZE, " +
            " COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) DATA1_DAY, " +
            " COUNT(STATUS=0 OR NULL) DATA2_SIZE, " +
            " COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) DATA2_DAY " +
            " FROM express ";
    // 用于查询数据库中的所有快递信息
    private static final String DQL_FIND_ALL = " SELECT * FROM express ";
    // 用于分页查询数据库中的所有快递信息
    private static final String DQL_FIND_LIMIT = " SELECT * FROM express LIMIT ?, ? ";
    // 通过单号查询快递信息
    private static final String DQL_FIND_BY_NUMBER = " SELECT * FROM express WHERE NUMBER=? ";
    // 通过取件码查询快递信息
    private static final String DQL_FIND_BY_CODE = " SELECT * FROM express WHERE CODE=? ";
    // 通过手机号码查询快递信息
    private static final String DQL_FIND_BY_USERPHONE_ALL = " SELECT * FROM express WHERE USERPHONE=? ";
    private static final String DQL_FIND_BY_USERPHONE_STATUS = " SELECT * FROM express WHERE USERPHONE=? AND STATUS = ? ";
    // 通过录入人手机号码查询快递信息
    private static final String DQL_FIND_BY_SYSPHONE = " SELECT * FROM express WHERE SYSPHONE=? ";

    // 可以加入一个通过用户姓名进行模糊查询
    private static final String DQL_FIND_BY_USERNAME = " SELECT COUNT(*) FROM express WHERE USERNAME like ? ";
    // 新增快递
    private static final String DML_INSERT = " INSERT INTO express(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,SYSPHONE,STATUS) VALUES(?,?,?,?,?,NOW(),?,0) ";
    // 修改快递(如果手机号码改了之后，利用ajax动态提醒 会收到新短信提醒)
    private static final String DML_UPDATE = " UPDATE express SET NUMBER=?,COMPANY=?,USERNAME=?,USERPHONE=? WHERE ID=? ";
    // 删除快递
    private static final String DML_DELETE = " DELETE FROM express WHERE ID=? ";
    // 修改快递状态(取件)
    private static final String DML_UPDATE_STATUS = " UPDATE express SET STATUS=1, OUTTIME=NOW(), CODE=NULL WHERE NUMBER=? ";
    // 根据id查找快递
    private static final String DQL_FIND_BY_ID = " SELECT * FROM express WHERE ID=? ";

    /**
     * 用于查询数据库中的全部快递(总数+新增) 待取件快递(总数+新增)
     *
     * @return [{size:总数, day:新增},{size:总数, day:新增}]
     */
    @Override
    public List<Integer> console() {
        List<Integer> integerList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1. 获取数据库的连接
            connection = DruidUtil.getConnection();
            // 2. 创建预状态通道
            preparedStatement = connection.prepareStatement(DQL_CONSOLE);
            // 3. 为SQL语句填充参数
            // 4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 5. 获取执行的结果
            while (resultSet.next()) {
                int data1_size = resultSet.getInt(1);
                int data1_day = resultSet.getInt(2);
                int data2_size = resultSet.getInt(3);
                int data2_day = resultSet.getInt(4);
                integerList.add(data1_size);
                integerList.add(data1_day);
                integerList.add(data2_size);
                integerList.add(data2_day);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 资源的释放
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return integerList;
    }

    /**
     * 用于分页查询数据库中的所有快递信息
     *
     * @param limit      true表示开启分页查询(默认) false 表示查询所有快递
     * @param offset     表示 SQL 语句起始索引
     * @param pageNumber 表示页查询快递数量
     * @return List<Express> 快递集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        List<Express> expresses = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1. 获取连接
            connection = DruidUtil.getConnection();
            // 2. 建立预状态通道
            if (!limit) {
                // 表示查询所有快递
                preparedStatement = connection.prepareStatement(DQL_FIND_ALL);
            } else {
                preparedStatement = connection.prepareStatement(DQL_FIND_LIMIT);
                // 3. 填充参数
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, pageNumber);
            }
            // 4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 5. 获得执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
                expresses.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return expresses;
    }

    /**
     * 根据快递运单号查询快递信息
     *
     * @param number 快递单号
     * @return Express 单号不存在时返回 null
     */
    @Override
    public Express findByNumber(String number) {
        Express express = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_NUMBER);
            preparedStatement.setString(1, number);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return express;
    }

    /**
     * 根据快递取件码查询快递信息
     *
     * @param code 快递取件码
     * @return Express 取件码不存在时返回 null
     */
    @Override
    public Express findByCode(String code) {
        Express express = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_CODE);
            preparedStatement.setString(1, code);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return express;
    }

    /**
     * 根据手机号码查询所有的快递信息
     *
     * @param userPhone 手机号码
     * @param status    快递状态 0表示查询待取件的快递(默认) 1表示查询已取件的快递 2 表示查询用户的所有快递
     * @return List<Express>
     */
    @Override
    public List<Express> findByUserPhone(String userPhone, int status) {
        List<Express> expresses = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1. 获取连接
            connection = DruidUtil.getConnection();
            // 2. 建立预状态通道
            if (status == 2) {
                preparedStatement = connection.prepareStatement(DQL_FIND_BY_USERPHONE_ALL);
                // 3. 填充参数 全部快递
                preparedStatement.setString(1, userPhone);
            } else if (status == 1) {
                preparedStatement = connection.prepareStatement(DQL_FIND_BY_USERPHONE_STATUS);
                // 3. 填充参数  已取件
                preparedStatement.setString(1, userPhone);
                preparedStatement.setInt(2, status);
            } else {
                preparedStatement = connection.prepareStatement(DQL_FIND_BY_USERPHONE_STATUS);
                // 3. 填充参数  未取件
                preparedStatement.setString(1, userPhone);
                preparedStatement.setInt(2, 0);
            }
            // 4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 5. 获得执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
                expresses.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return expresses;
    }

    /**
     * 根据录入人的手机号，查询快递信息
     *
     * @param sysPhone 录入人的手机号码
     * @return List<Express>
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> expresses = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1. 获取连接
            connection = DruidUtil.getConnection();
            // 2. 建立预状态通道
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_SYSPHONE);
            // 3. 填充参数
            preparedStatement.setString(1, sysPhone);
            // 4. 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 5. 获得执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
                expresses.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return expresses;
    }

    /**
     * 根据姓名，进行模糊查询快递总数
     *
     * @param username 用户姓名
     * @return Integer
     */
    @Override
    public Integer findByUsername(String username) {
        Integer integer = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_USERNAME);
            preparedStatement.setString(1, "%" + username + "%");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                integer = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return integer;
    }

    /**
     * 根据id查询快递
     *
     * @param id
     * @return
     */
    @Override
    public Express findById(int id) {
        Express express = new Express();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;

        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DQL_FIND_BY_ID);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUsername(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setStatus(resultSet.getInt("status"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return express;
    }

    /**
     * 快件录入
     *
     * @param express 快递对象
     * @return true表示录入成功  false表示录入失败
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_INSERT);
            preparedStatement.setString(1, express.getNumber());
            preparedStatement.setString(2, express.getUsername());
            preparedStatement.setString(3, express.getUserPhone());
            preparedStatement.setString(4, express.getCompany());
            preparedStatement.setString(5, express.getCode());
            preparedStatement.setString(6, express.getSysPhone());

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            /*e.printStackTrace();*/
            System.out.println(e.getMessage());
            if (e.getMessage().endsWith("for key 'code'")) {
                // 是因为取件码重复导致的异常
                DuplicateCodeException duplicateCodeException = new DuplicateCodeException(e.getMessage());
                throw duplicateCodeException;
            } else {
                e.printStackTrace();
            }
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i > 0 ? true : false;
    }

    /**
     * 修改快递信息
     * String number,
     * String company,
     * String username,
     * String userPhone,
     * int status
     *
     * @param id         要修改的快递id
     * @param newExpress 新的快递对象
     * @return true 表示修改成功 false 表示修改失败
     */
    @Override
    public boolean update(int id, Express newExpress) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_UPDATE);
            preparedStatement.setString(1, newExpress.getNumber());
            preparedStatement.setString(2, newExpress.getCompany());
            preparedStatement.setString(3, newExpress.getUsername());
            preparedStatement.setString(4, newExpress.getUserPhone());
            preparedStatement.setInt(5, id);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i > 0 ? true : false;
    }

    /**
     * 根据快递id删除快递信息
     *
     * @param id 要删除的快递id
     * @return true表示删除成功 false 表示删除失败
     */
    @Override
    public boolean delete(int id) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_DELETE);
            preparedStatement.setInt(1, id);
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return i > 0 ? true : false;

    }

    /**
     * 确认取件
     *
     * @param number 要更改为已取件的快递单号
     * @return true表示已成功取件 false 表示取件失败
     */
    @Override
    public boolean updateStatus(String number) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        int i = 0;
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(DML_UPDATE_STATUS);
            preparedStatement.setString(1, number);
            i = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }

        return i > 0 ? true : false;
    }
}
